#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：data_filter-master
@File ：database_handler.py
@IDE  ：PyCharm
@Author ：李涵彬
@Date ：2024/2/23 23:13
"""

import pyodbc
from typing import Dict, List

# 字段映射表
FIELD_MAPPING: Dict[str, str] = {
	"en_field1": "中文字段1",
	"en_field2": "中文字段2",
	# 添加更多的映射关系
}


class DatabaseHandler:
	"""SQL Server数据库处理器"""

	def __init__(self, host: str, user: str, password: str, database: str):
		"""
		初始化SQL Server连接

		Args:
			host (str): 主机地址
			user (str): 用户名
			password (str): 密码
			database (str): 数据库名

		Raises:
			pyodbc.Error: 连接数据库出错时引发异常
		"""
		try:
			# 创建连接字符串
			connection_string = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={host};DATABASE={database};UID={user};PWD={password}"
			# 创建连接
			self.connection = pyodbc.connect(connection_string)
		except pyodbc.Error as e:
			print(f"连接数据库出错: {e}")
			raise

	def fetch_data(self, table_name: str) -> List[Dict[str, any]]:
		"""
		查询数据并进行字段名映射

		Args:
			table_name (str): 表名

		Returns:
			List[Dict[str, any]]: 查询结果的字典列表
		"""
		try:
			# 获取游标
			cursor = self.connection.cursor()
			# 构建查询语句，假设所有字段都需要查询
			query = f"SELECT * FROM {table_name}"
			cursor.execute(query)
			# 获取查询结果
			result = cursor.fetchall()
		finally:
			# 关闭游标
			cursor.close()

		# 将英文字段名转换为中文字段名
		mapped_result = self.map_fields(result)

		return mapped_result

	def map_fields(self, data: List[Dict[str, any]]) -> List[Dict[str, any]]:
		"""
		将查询结果中的英文字段名称转换为中文字段名称

		Args:
			data (List[Dict[str, any]]): 查询结果的字典列表

		Returns:
			List[Dict[str, any]]: 映射后的查询结果
		"""
		mapped_data = []
		for row in data:
			mapped_row = {}
			for key, value in row.items():
				if key in FIELD_MAPPING:
					mapped_row[FIELD_MAPPING[key]] = value
				else:
					mapped_row[key] = value
			mapped_data.append(mapped_row)
		return mapped_data
